package cn.easyplatform.studio.dao.impl;

import cn.easyplatform.lang.Strings;
import cn.easyplatform.studio.dao.DaoException;
import cn.easyplatform.studio.dao.DaoUtils;
import cn.easyplatform.studio.dao.DatabaseUpdateDao;
import cn.easyplatform.studio.dao.Dialect;
import cn.easyplatform.studio.dao.transaction.JdbcTransactions;
import cn.easyplatform.studio.utils.FileUtil;
import cn.easyplatform.studio.vos.*;
import cn.easyplatform.type.FieldType;
import org.apache.commons.lang3.RandomStringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.management.relation.RoleInfo;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public abstract class AbstractDatabaseUpdateDao implements DatabaseUpdateDao {
    protected final static Logger log = LoggerFactory.getLogger(AbstractVersionDao.class);

    protected DataSource ds;

    /**
     * @param ds
     */
    public AbstractDatabaseUpdateDao(DataSource ds) {
        this.ds = ds;
    }

    protected abstract Dialect getDialect();
    @Override
    public void createSecondVersion(String table) {
        PreparedStatement pstmt = null;
        try {
            //移动端导出
            Connection conn = JdbcTransactions.getConnection(ds);
            List<CreateTableVo.TableField> tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "confId", FieldType.INT, 0,
                    true, false, true, false, false));
            tableFields.add(new CreateTableVo.TableField( "confType", FieldType.VARCHAR, 2,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "bundleId", FieldType.VARCHAR, 40,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "displayName", FieldType.VARCHAR, 40,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "version", FieldType.VARCHAR, 40,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "icon", FieldType.VARCHAR, 400,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "launch", FieldType.VARCHAR, 800,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "url", FieldType.VARCHAR, 200,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "createUserId", FieldType.VARCHAR, 40,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                    false, false, false, true, false));
            CreateTableVo tableVo = new CreateTableVo( table + "_mobile_conf_info", tableFields);
            StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
            log.debug(sb.toString());


            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX ").append(table).append("_mobile_conf_info_idx ON ")
                    .append(table).append("_mobile_conf_info(confId)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //导入文件历史
            List<CreateTableVo.TableField> uploadTableFields = new ArrayList<>();
            uploadTableFields.add(new CreateTableVo.TableField( "id", FieldType.INT, 0,
                    true, false, true, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "fileName", FieldType.VARCHAR, 100,
                    false, false, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "uploadData", FieldType.BLOB, 0,
                    false, false, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "packName", FieldType.VARCHAR, 100,
                    false, false, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "type", FieldType.VARCHAR, 100,
                    false, true, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "contentType", FieldType.VARCHAR, 100,
                    false, true, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "projectName", FieldType.VARCHAR, 100,
                    false, true, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "uploadUser", FieldType.VARCHAR, 100,
                    false, false, false, false, false));
            uploadTableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                    false, false, false, true, false));
            CreateTableVo uploadTableVo = new CreateTableVo( "ep_upload_file_hist", uploadTableFields);
            sb = DaoUtils.createTable(uploadTableVo, getDialect());
            log.debug(sb.toString());
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX ep_upload_file_hist_idx ON ep_upload_file_hist(id)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //关系列表
            List<CreateTableVo.TableField> linkTableFields = new ArrayList<>();
            linkTableFields.add(new CreateTableVo.TableField( "entityId", FieldType.VARCHAR, 40,
                    true, false, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "childrenEntityId", FieldType.VARCHAR, 800,
                    false, true, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "entityName", FieldType.VARCHAR, 100,
                    false, false, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "entityType", FieldType.VARCHAR, 10,
                    false, false, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "entitySubType", FieldType.VARCHAR, 10,
                    false, true, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "entityDesp", FieldType.VARCHAR, 200,
                    false, true, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                    false, false, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "updateDate", FieldType.DATETIME, 0,
                    false, true, false, false, true));
            linkTableFields.add(new CreateTableVo.TableField( "createUser", FieldType.VARCHAR, 20,
                    false, false, false, false, false));
            linkTableFields.add(new CreateTableVo.TableField( "updateUser", FieldType.VARCHAR, 20,
                    false, true, false, false, false));
            CreateTableVo linkTableVo = new CreateTableVo( table + "_link", linkTableFields);
            linkTableFields.add(new CreateTableVo.TableField( "status", FieldType.VARCHAR, 2,
                    false, false, false, false, false));
            sb = DaoUtils.createTable(linkTableVo, getDialect());
            log.debug(sb.toString());
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX " + table + "_link_idx ON " + table + "_link(entityId)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //关系列表
            List<CreateTableVo.TableField> taskTableFields = new ArrayList<>();
            taskTableFields.add(new CreateTableVo.TableField( "entityId", FieldType.VARCHAR, 40,
                    true, false, false, false, false));
            taskTableFields.add(new CreateTableVo.TableField( "childrenEntityId", FieldType.VARCHAR, 800,
                    false, true, false, false, false));
            taskTableFields.add(new CreateTableVo.TableField( "isRoot", FieldType.VARCHAR, 2,
                    false, false, false, false, false));

            CreateTableVo taskTableVo = new CreateTableVo( table + "_task_link", taskTableFields);
            sb = DaoUtils.createTable(taskTableVo, getDialect());
            log.debug(sb.toString());
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX " + table + "_task_link_idx ON " + table + "_task_link(entityId)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createSecondOneVersion(String table, String projectId, boolean isContainNewMobileExport,
                                       boolean isContainGuideInfo){
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            //改表名
            pstmt = conn.prepareStatement("alter table " + table + "_link_info rename as " + table + "_link");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            pstmt = conn.prepareStatement("alter table " + table + "_task_link_info rename as " + table + "_task_link");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            //新建移动端导出
            if (isContainNewMobileExport == false) {
                List<CreateTableVo.TableField> tableFields = new ArrayList<>();
                tableFields.add(new CreateTableVo.TableField( "confId", FieldType.INT, 0,
                        true, false, true, false, false));
                tableFields.add(new CreateTableVo.TableField( "projectId", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "confType", FieldType.VARCHAR, 2,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "bundleId", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "displayName", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "version", FieldType.VARCHAR, 40,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "icon", FieldType.VARCHAR, 400,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "launch", FieldType.VARCHAR, 800,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "url", FieldType.VARCHAR, 200,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createUserId", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                        false, false, false, true, false));
                tableFields.add(new CreateTableVo.TableField( "updateUserId", FieldType.VARCHAR, 40,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "updateDate", FieldType.DATE, 0,
                        false, true, false, false, false));
                CreateTableVo tableVo = new CreateTableVo( "ep_mobile_conf_info", tableFields);
                StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
                log.debug(sb.toString());
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
                pstmt = null;
                sb.setLength(0);
                //索引
                sb.append("CREATE INDEX ").append("ep_mobile_conf_info_idx ON ep_mobile_conf_info(confId)");
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
            }
            //获取原数据
            pstmt = conn.prepareStatement("select a.confId,confType,bundleId,displayName,version,icon,launch,url," +
                    "createUserId,createDate from " + table + "_mobile_conf_info a," +
                    "(SELECT MAX(confId) AS confId FROM " + table + "_mobile_conf_info GROUP BY confType) b where a.confId=b.confId");
            log.debug("原数据");
            rs = pstmt.executeQuery();
            List<MobileConfVo> voList = new ArrayList<>();
            while (rs.next()) {
                MobileConfVo vo = new MobileConfVo(rs.getInt(1), rs.getString(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9),
                        rs.getDate(10));
                voList.add(vo);
            }
            pstmt.close();
            pstmt = null;
            //填入数据
            if (voList.size() > 0) {
                pstmt = conn
                        .prepareStatement("insert into ep_mobile_conf_info (projectId,confType,bundleId,displayName,version,icon,launch,url,createUserId) " +
                                "values(?,?,?,?,?,?,?,?,?)");
                for (MobileConfVo confVo: voList) {
                    pstmt.setString(1, projectId);
                    pstmt.setString(2, confVo.getConfType());
                    pstmt.setString(3, confVo.getBundleId());
                    pstmt.setString(4, confVo.getDisplayName());
                    pstmt.setString(5, confVo.getMobileVersion());
                    pstmt.setString(6, confVo.getIcon());
                    pstmt.setString(7, confVo.getLaunch());
                    pstmt.setString(8, confVo.getUrl());
                    pstmt.setString(9, confVo.getCreateUserId());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
            //删除表
            pstmt = conn.prepareStatement("DROP TABLE " + table + "_mobile_conf_info");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            if (isContainGuideInfo == false) {
                //添加引导信息
                List<CreateTableVo.TableField> tableFields = new ArrayList<>();
                tableFields.add(new CreateTableVo.TableField( "guideID", FieldType.INT, 0,
                        true, false, true, false, false));
                tableFields.add(new CreateTableVo.TableField( "guideName", FieldType.VARCHAR, 80,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "guideStep", FieldType.CLOB, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "guideNextID", FieldType.VARCHAR, 80,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "isRoot", FieldType.VARCHAR, 2,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                        false, false, false, true, false));
                tableFields.add(new CreateTableVo.TableField( "updateDate", FieldType.DATE, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createUser", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "updateUser", FieldType.VARCHAR, 40,
                        false, true, false, false, false));
                CreateTableVo tableVo = new CreateTableVo( "ep_guide_info", tableFields);
                StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
                pstmt = null;
                sb.setLength(0);
                //索引
                sb.append("CREATE INDEX ").append("ep_guide_info_idx ON ep_guide_info(guideID)");
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt, rs);
        }
    }


    @Override
    public void createSecondSecondVersion(DataSource dataSource, boolean isContainSysGuideInfo) {
        if (isContainSysGuideInfo == false) {
            PreparedStatement pstmt = null;
            try {
                Connection conn = JdbcTransactions.getConnection(dataSource);
                //新建移动端导出
                List<CreateTableVo.TableField> tableFields = new ArrayList<>();
                tableFields.add(new CreateTableVo.TableField( "guideID", FieldType.INT, 0,
                        true, false, true, false, false));
                tableFields.add(new CreateTableVo.TableField( "guideName", FieldType.VARCHAR, 80,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "guideStep", FieldType.CLOB, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "guideNextID", FieldType.VARCHAR, 80,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "isRoot", FieldType.VARCHAR, 2,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                        false, false, false, true, false));
                tableFields.add(new CreateTableVo.TableField( "updateDate", FieldType.DATE, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createUser", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "updateUser", FieldType.VARCHAR, 40,
                        false, true, false, false, false));
                CreateTableVo tableVo = new CreateTableVo( "sys_guide_info", tableFields);
                StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
                pstmt = null;
                sb.setLength(0);
                //索引
                sb.append("CREATE INDEX ").append("sys_guide_info_idx ON sys_guide_info(guideID)");
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
            } catch (SQLException ex) {
                throw new DaoException(ex.getMessage());
            } finally {
                DaoUtils.closeQuietly(pstmt);
            }
        }
    }

    @Override
    public void createSecondThirdVersion(String table, boolean isContainModule) {
        if (isContainModule == false) {
            PreparedStatement pstmt = null;
            try {
                Connection conn = JdbcTransactions.getConnection(ds);
                //新建移动端导出
                List<CreateTableVo.TableField> tableFields = new ArrayList<>();
                tableFields.add(new CreateTableVo.TableField( "moduleId", FieldType.VARCHAR, 40,
                        true, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "moduleName", FieldType.VARCHAR, 80,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "price", FieldType.VARCHAR, 20,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "desp", FieldType.CLOB, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "pics", FieldType.BLOB, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "entityIds", FieldType.CLOB, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "chooseType", FieldType.VARCHAR, 2,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "rootEntityIds", FieldType.CLOB, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                        false, false, false, true, false));
                tableFields.add(new CreateTableVo.TableField( "updateDate", FieldType.DATE, 0,
                        false, true, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "createUser", FieldType.VARCHAR, 40,
                        false, false, false, false, false));
                tableFields.add(new CreateTableVo.TableField( "updateUser", FieldType.VARCHAR, 40,
                        false, true, false, false, false));
                CreateTableVo tableVo = new CreateTableVo( table + "_module", tableFields);
                StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
                pstmt = null;
                sb.setLength(0);
                //索引
                sb.append("CREATE INDEX ").append(table + "_module_idx ON " + table + "_module(moduleId)");
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.execute();
                pstmt.close();
            } catch (SQLException ex) {
                throw new DaoException(ex.getMessage());
            } finally {
                DaoUtils.closeQuietly(pstmt);
            }
        }
    }

    @Override
    public void createSecondSevenVersion(DataSource dataSource, String table, String userID) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            //新建移动端导出
            //alter table ep_eqb_module add column dicDetailID varchar(12) null after rootEntityIds;
            pstmt = conn.prepareStatement("alter table " + table + "_module add column dicDetailID varchar(12) null after rootEntityIds");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }

        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            List<DicVo> dicVos = new ArrayList<>();
            List<DicVo> allVos = new ArrayList<>();
            DicVo firstVo = new DicVo("90", userID, "系统专属", null, "1", "系统专属");
            DicVo secondVo = new DicVo("90100", userID, "系统功能的选项", "90", "2", "系统功能的选项");
            DicVo thirdVo = new DicVo("9010010018", userID, "产品模块设置产品归属", "90100", "4", "产品模块设置产品归属");
            dicVos.add(firstVo);
            dicVos.add(secondVo);
            dicVos.add(thirdVo);
            for (DicVo e : dicVos) {
                pstmt = conn.prepareStatement("select * from sys_dic_info where diccode=?");
                pstmt.setString(1, e.getDicCode());
                rs = pstmt.executeQuery();
                boolean isContain = true;
                while (rs.next()) {
                    isContain = false;
                }
                if (isContain == true)
                    allVos.add(e);
                pstmt.close();
                pstmt = null;
            }

            if (allVos.size() > 0) {
                pstmt = conn
                        .prepareStatement("INSERT INTO sys_dic_info(diccode, createuser, createdate, desc1, upcode, " +
                                "diclevel, displayflag, editableflag, dicstatus, zh_cn, zh_tw, en_us, ja_jp, ko_kr, " +
                                "de_de, fr_fr, it_it) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                for (DicVo dicVo: allVos) {
                    pstmt.setString(1, dicVo.getDicCode());
                    pstmt.setString(2, userID);
                    pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
                    pstmt.setString(4, dicVo.getDesc1());
                    pstmt.setString(5, dicVo.getUpCode());
                    pstmt.setString(6, dicVo.getDicLevel());
                    pstmt.setString(7, dicVo.getDisplayFlag());
                    pstmt.setString(8, dicVo.getEditableFlag());
                    pstmt.setString(9, dicVo.getDicStatus());
                    pstmt.setString(10, dicVo.getZh_cn());
                    pstmt.setString(11, dicVo.getZh_tw());
                    pstmt.setString(12, dicVo.getEn_us());
                    pstmt.setString(13, dicVo.getJa_jp());
                    pstmt.setString(14, dicVo.getKo_kr());
                    pstmt.setString(15, dicVo.getDe_de());
                    pstmt.setString(16, dicVo.getFr_fr());
                    pstmt.setString(17, dicVo.getIt_it());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createSecondSeventeenVersion(DataSource dataSource) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            //新建移动端导出
            //alter table ep_eqb_module add column dicDetailID varchar(12) null after rootEntityIds;
            pstmt = conn.prepareStatement("alter table sys_role_info add column `type` varchar(10) null after roleId");
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("alter table sys_role_info add column tid varchar(40) null after desp");
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("alter table sys_menu_info add column tid varchar(40) null after desp");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createSecondEighteenVersion(String table) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            List<CreateTableVo.TableField> tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "logId", FieldType.LONG, 0,
                    true, false, true, false, false));
            tableFields.add(new CreateTableVo.TableField( "username", FieldType.VARCHAR, 80,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "logintime", FieldType.DATETIME, 80,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "ipaddress", FieldType.VARCHAR, 80,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "loginos", FieldType.VARCHAR, 80,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "logintn", FieldType.VARCHAR, 80,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "logintype", FieldType.VARCHAR, 80,
                    false, true, false, false, false));

            CreateTableVo tableVo = new CreateTableVo( table, tableFields);
            StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX ").append(table).append("_idx ON ")
                    .append(table).append("(logId)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeVersion(DataSource dataSource, List<String> fieldNameList, List<String> fieldTypeList,
                                   List<String> fieldLengthList, List<String> fieldDesList, List<String> fieldNotNUllList) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            //获取sys_message_info表结构

            String tableId = "sys_message_info";
            String tableName = "标签表";

            if (fieldNameList.size() > 0) {
                pstmt = conn
                        .prepareStatement("INSERT INTO sys_table_field_info(table1id, table1name, field1name, field1type, " +
                                "field1length, field1decimal, field1description, field1notnull, field1orderNo) VALUES" +
                                " (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                for (int index = 0; index < fieldNameList.size(); index++) {
                    pstmt.setString(1, tableId);
                    pstmt.setString(2, tableName);
                    pstmt.setString(3, fieldNameList.get(index));
                    pstmt.setString(4, fieldTypeList.get(index));
                    pstmt.setString(5, fieldLengthList.get(index));
                    pstmt.setString(6, "0");
                    pstmt.setString(7, fieldDesList.get(index));
                    pstmt.setString(8, fieldNotNUllList.get(index));
                    pstmt.setString(9, Integer.toString(index + 1));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeOneVersion() {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            //新建移动端导出
            //alter table ep_import_detail_info add column targetVersionNo int(32) null after versionNo;
            pstmt = conn.prepareStatement("alter table ep_import_detail_info add column `targetVersionNo` int(32) null after versionNo");
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement("select * from ep_import_detail_info");
            rs = pstmt.executeQuery();
            List<String> idList = new ArrayList<>();
            List<Integer> versionList = new ArrayList<>();
            while (rs.next()) {
                idList.add(rs.getString(1));
                versionList.add(rs.getInt(2));
            }
            pstmt.close();
            pstmt = null;

            if (idList.size() > 0) {
                //update ep_import_detail_info SET `targetVersionNo` = 222 WHERE `txId` = 'Wilson'
                pstmt = conn
                        .prepareStatement("update ep_import_detail_info set `targetVersionNo` = ? " +
                                "where `txId` = ? and `versionNo` = ?");
                for (int index = 0; index < idList.size(); index++) {
                    pstmt.setInt(1, versionList.get(index));
                    pstmt.setString(2, idList.get(index));
                    pstmt.setInt(3, versionList.get(index));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeTwoVersion(DataSource dataSource, List<String> fieldNameList, List<String> fieldTypeList,
                                      List<String> fieldLengthList, List<String> fieldDesList, List<String> fieldNotNUllList) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            //新建移动端导出
            //alter table ep_import_detail_info add column targetVersionNo int(32) null after versionNo;
            pstmt = conn.prepareStatement("DELETE FROM sys_table_field_info WHERE table1id = 'sys_message_info' ");
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            String tableId = "sys_message_info";
            String tableName = "标签表";

            if (fieldNameList.size() > 0) {
                pstmt = conn
                        .prepareStatement("INSERT INTO sys_table_field_info(table1id, table1name, field1name, field1type, " +
                                "field1length, field1decimal, field1description, field1notnull, field1orderNo) VALUES" +
                                " (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                for (int index = 0; index < fieldNameList.size(); index++) {
                    pstmt.setString(1, tableId);
                    pstmt.setString(2, tableName);
                    pstmt.setString(3, fieldNameList.get(index));
                    pstmt.setString(4, fieldTypeList.get(index));
                    pstmt.setString(5, fieldLengthList.get(index));
                    pstmt.setString(6, "0");
                    pstmt.setString(7, fieldDesList.get(index));
                    pstmt.setString(8, fieldNotNUllList.get(index));
                    pstmt.setString(9, Integer.toString(index + 1));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeThreeVersion(DataSource dataSource) {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        Connection conn = null;
        ResultSet rs = null;
        String tmpTable1 = null;
        try {
            conn = JdbcTransactions.getConnection(dataSource);
            tmpTable1 = backup("sys_role_menu_info", dataSource);
            // 删除原有的表
            StringBuffer sb = new StringBuffer();
            sb.append("DROP TABLE ").append("sys_role_menu_info");
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
            stmt.close();
            stmt = null;
            System.out.println("DROP:" + sb.toString());
            //创建表
            //第二个表
            List<CreateTableVo.TableField> tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "roleId", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "menuId", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "orderNo", FieldType.VARCHAR, 32,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "type", FieldType.VARCHAR, 10,
                    true, false, false, false, false));

            CreateTableVo tableVo = new CreateTableVo( "sys_role_menu_info", tableFields);
            StringBuilder sb1 = DaoUtils.createTable(tableVo, getDialect());
            pstmt = conn.prepareStatement(sb1.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb1.setLength(0);
            //索引
            //第二个表
            sb1.append("CREATE INDEX ").append("sys_role_menu_info_idx on sys_role_menu_info (roleId,menuId,type)");
            pstmt = conn.prepareStatement(sb1.toString());
            pstmt.execute();
            pstmt.close();
            sb1.setLength(0);
            //insert
            pstmt = conn
                    .prepareStatement("select roleId,`name`,image,desp,`type` from sys_role_info where 1 = 1");
            List<RoleVo> roleVos = new ArrayList<>();
            rs = pstmt.executeQuery();
            while (rs.next()) {
                RoleVo vo = new RoleVo();
                vo.setId(rs.getString(1));
                vo.setName(rs.getString(2));
                vo.setImage(rs.getString(3));
                vo.setDesp(rs.getString(4));
                vo.setType(rs.getString(5));
                roleVos.add(vo);
            }
            pstmt.close();
            pstmt = null;
            //第二张表
            pstmt = conn
                    .prepareStatement("select roleId,menuId,orderNo from " + tmpTable1  + " where 1 = 1");
            List<RoleMenuVo> roleMenuVos = new ArrayList<>();
            rs = pstmt.executeQuery();
            while (rs.next()) {
                RoleMenuVo vo = new RoleMenuVo();
                vo.setRoleId(rs.getString(1));
                vo.setMenuId(rs.getString(2));
                vo.setOrderNo(rs.getString(3));
                roleMenuVos.add(vo);
            }
            for (RoleMenuVo menuVo : roleMenuVos) {
                menuVo.setType("mil");
                for (RoleVo roleVo : roleVos) {
                    if (roleVo.getId().equals(menuVo.getRoleId())) {
                        if (Strings.isBlank(roleVo.getType()) == true || "desk".equals(roleVo.getType())) {
                            menuVo.setType("ajax");
                            break;
                        }
                    }
                }
            }
            pstmt.close();
            pstmt = null;
            if (roleMenuVos.size() > 0) {
                pstmt = conn
                        .prepareStatement("INSERT INTO sys_role_menu_info(roleId, menuId, orderNo, `type`) VALUES" +
                                " (?, ?, ?, ?)");
                for (RoleMenuVo vo: roleMenuVos) {
                    pstmt.setString(1, vo.getRoleId());
                    pstmt.setString(2, vo.getMenuId());
                    pstmt.setString(3, vo.getOrderNo());
                    pstmt.setString(4, vo.getType());
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException ex) {
            if (Strings.isBlank(tmpTable1) == false)
                restore(tmpTable1, "sys_role_menu_info", dataSource);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (tmpTable1 != null) {// 必须清除临时表
                try {
                    stmt = conn.createStatement();
                    stmt.execute("DROP TABLE " + tmpTable1);
                } catch (Exception ex) {
                } finally {
                    DaoUtils.closeQuietly(stmt);
                }
            }
        }
    }
    @Override
    public void createThreeFourVersion() {

        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            List<CreateTableVo.TableField> tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "id", FieldType.LONG, 0,
                    true, false, true, false, false));
            tableFields.add(new CreateTableVo.TableField( "projectId", FieldType.VARCHAR, 20,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "content", FieldType.CLOB, 0,
                    false, true, false, false, false));


            CreateTableVo tableVo = new CreateTableVo( "ep_model_ext_info", tableFields);
            StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX ").append("ep_model_ext_info_idx on ep_model_ext_info (id)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeFiveVersion(String table) {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        Connection conn = null;
        String tmpTable1 = null;
        try {
            conn = JdbcTransactions.getConnection(ds);
            tmpTable1 = backup(table, ds);
            pstmt = conn.prepareStatement("ALTER TABLE " + table +
                    " ADD COLUMN `ext0` mediumtext AFTER `content`");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            pstmt = conn.prepareStatement("ALTER TABLE " + table +
                    " ADD COLUMN `ext1` mediumtext AFTER `ext0`");
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            if (Strings.isBlank(tmpTable1))
                restore(tmpTable1, table, ds);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (tmpTable1 != null) {// 必须清除临时表
                try {
                    stmt = conn.createStatement();
                    stmt.execute("DROP TABLE " + tmpTable1);
                } catch (Exception ex) {
                } finally {
                    DaoUtils.closeQuietly(stmt);
                }
            }
        }
    }

    @Override
    public void createThreeSixVersion(DataSource dataSource) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            //新建角色授权表
            List<CreateTableVo.TableField> tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "roleId", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "taskId", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "type", FieldType.VARCHAR, 10,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "access", FieldType.VARCHAR, 250,
                    false, true, false, false, false));
            CreateTableVo tableVo = new CreateTableVo( "sys_role_access_info", tableFields);
            StringBuilder sb = DaoUtils.createTable(tableVo, getDialect());
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb.setLength(0);
            //索引
            sb.append("CREATE INDEX ").append("sys_role_access_info_idx ON sys_role_access_info(roleId, taskId, type)");
            pstmt = conn.prepareStatement(sb.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeSevenVersion(DataSource dataSource) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            //新建角色表
            List<CreateTableVo.TableField> secondTableFields = new ArrayList<>();
            secondTableFields.add(new CreateTableVo.TableField( "code", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "name", FieldType.VARCHAR, 50,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "remark", FieldType.VARCHAR, 255,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "controlType", FieldType.VARCHAR, 50,
                    true, false, false, false, false));
            CreateTableVo secondTableVo = new CreateTableVo( "sys_access_info", secondTableFields);
            StringBuilder secondSB = DaoUtils.createTable(secondTableVo, getDialect());
            pstmt = conn.prepareStatement(secondSB.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            secondSB.setLength(0);
            //索引
            secondSB.append("CREATE INDEX ").append("sys_access_info_idx ON sys_access_info(code, controlType)");
            pstmt = conn.prepareStatement(secondSB.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createThreeEightVersion() {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        Connection conn = null;
        String tmpTable1 = null;
        String tmpTable2 = null;
        ResultSet rs = null;
        try {
            conn = JdbcTransactions.getConnection(ds);
            tmpTable1 = backup("ep_user_project_info", ds);

            pstmt = conn.prepareStatement("SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE where " +
                    "constraint_name='ep_user_project_info_ibfk_1' and CONSTRAINT_SCHEMA=?");
            pstmt.setString(1, conn.getCatalog());
            rs = pstmt.executeQuery();
            int count = 0;
            while (rs.next()) {
                count = rs.getInt(1);
            }
            pstmt.close();
            pstmt = null;
            if (count >= 1) {
                pstmt = conn.prepareStatement("ALTER TABLE ep_user_project_info drop foreign key ep_user_project_info_ibfk_1");
                pstmt.execute();
                pstmt.close();
                pstmt = null;
            }
            tmpTable2 = backup("ep_user_info", ds);
            // 删除原有的表
            StringBuffer sb = new StringBuffer();
            sb.append("DROP TABLE ").append("ep_user_info");
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
            stmt.close();
            stmt = null;
            System.out.println("DROP:" + sb.toString());
            //创建表
            //userId |name |password |type|validDate|validDays|loginFailedTimes|lastLoginTime |lastLogoutTime|ipAddress|salt|theme|editorTheme|roleId|state
            List<CreateTableVo.TableField> tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "userId", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "name", FieldType.VARCHAR, 20,
                    false, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "password", FieldType.VARCHAR, 80,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "validDate", FieldType.DATE, 0,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "validDays", FieldType.INT, 32,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "loginFailedTimes", FieldType.INT, 32,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "lastLoginTime", FieldType.DATE, 0,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "lastLogoutTime", FieldType.DATE, 0,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "ipAddress", FieldType.VARCHAR, 20,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "salt", FieldType.VARCHAR, 20,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "theme", FieldType.VARCHAR, 20,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "editorTheme", FieldType.VARCHAR, 20,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "state", FieldType.INT, 32,
                    false, true, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "defaultProject", FieldType.VARCHAR, 32,
                    false, true, false, false, false));

            CreateTableVo tableVo = new CreateTableVo( "ep_user_info", tableFields);
            StringBuilder sb1 = DaoUtils.createTable(tableVo, getDialect());
            pstmt = conn.prepareStatement(sb1.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb1.setLength(0);
            //索引
            sb1.append("CREATE INDEX ").append("ep_user_info_idx on ep_user_info (userId)");
            pstmt = conn.prepareStatement(sb1.toString());
            pstmt.execute();
            pstmt.close();
            sb1.setLength(0);
            //insert
            stmt = conn.createStatement();
            stmt.execute("insert into ep_user_info(userId,`name`,password,validDate,validDays,loginFailedTimes," +
                    "lastLoginTime,lastLogoutTime,ipAddress,salt,theme,editorTheme,state) " +
                    "select userId,`name`,password,validDate,validDays,loginFailedTimes," +
                    "lastLoginTime,lastLogoutTime,ipAddress,salt,theme,editorTheme,state from " + tmpTable2);
            stmt.close();
            stmt = null;
            // 删除原有的表
            sb = new StringBuffer();
            sb.append("DROP TABLE ").append("ep_user_project_info");
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
            stmt.close();
            stmt = null;
            System.out.println("DROP:" + sb.toString());
            //创建表
            //userId |name |password |type|validDate|validDays|loginFailedTimes|lastLoginTime |lastLogoutTime|ipAddress|salt|theme|editorTheme|roleId|state
            tableFields = new ArrayList<>();
            tableFields.add(new CreateTableVo.TableField( "userId", FieldType.VARCHAR, 20,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "projectId", FieldType.VARCHAR, 40,
                    true, false, false, false, false));
            tableFields.add(new CreateTableVo.TableField( "roleId", FieldType.VARCHAR, 999,
                    false, true, false, false, false));

            tableVo = new CreateTableVo( "ep_user_project_info", tableFields);
            sb1 = DaoUtils.createTable(tableVo, getDialect());
            pstmt = conn.prepareStatement(sb1.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            sb1.setLength(0);
            //索引
            sb1.append("CREATE INDEX ").append("ep_user_project_info_idx on ep_user_project_info (userId,projectId)");
            pstmt = conn.prepareStatement(sb1.toString());
            pstmt.execute();
            pstmt.close();
            sb1.setLength(0);
            //insert
            pstmt = conn.prepareStatement("insert into ep_user_project_info(userId,projectId,roleId) select userId,projectId,'DEV,admin' as `roleId` from " + tmpTable1);
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
            if (Strings.isBlank(tmpTable1) == false)
                restore(tmpTable1, "ep_user_project_info", ds);
            if (Strings.isBlank(tmpTable2) == false)
                restore(tmpTable2, "ep_user_info", ds);
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
            if (tmpTable1 != null) {// 必须清除临时表
                try {
                    stmt = conn.createStatement();
                    stmt.execute("DROP TABLE " + tmpTable1);
                } catch (Exception ex) {
                } finally {
                    DaoUtils.closeQuietly(stmt);
                }
            }
            if (tmpTable2 != null) {// 必须清除临时表
                try {
                    stmt = conn.createStatement();
                    stmt.execute("DROP TABLE " + tmpTable2);
                } catch (Exception ex) {
                } finally {
                    DaoUtils.closeQuietly(stmt);
                }
            }
        }
    }

    @Override
    public void createThreeNineVersion() {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            /*INSERT INTO table(field1, field2, fieldn) SELECT 'field1',
            'field2', 'fieldn' FROM DUAL WHERE NOT EXISTS(SELECT field FROM
            table WHERE field = ?)*/
            conn = JdbcTransactions.getConnection(ds);
            pstmt = conn
                    .prepareStatement("select modelId from ep_model_info where type='Project' and 1=1");
            rs = pstmt.executeQuery();
            List<String> data = new ArrayList<>();
            while (rs.next()) {
                data.add(rs.getString(1));
            }

            if (data.size() > 0) {
                pstmt = conn
                        .prepareStatement("INSERT INTO ep_role_info(roleId, projectId, name, desp, permissions) SELECT " +
                                "?,?,?,?,? FROM DUAL WHERE NOT EXISTS(SELECT roleId, projectId FROM ep_role_info WHERE roleId=? AND projectId=?)");
                for (int index = 0; index < data.size(); index++) {
                    pstmt.setString(1, LoginUserVo.UserType.DEV.getName());
                    pstmt.setString(2, data.get(index));
                    pstmt.setString(3, "默认开发者");
                    pstmt.setString(4, "系统自带全功能开发者");
                    pstmt.setString(5, new FileUtil().getAllRole());
                    pstmt.setString(6, "DEV");
                    pstmt.setString(7, data.get(index));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    @Override
    public void createFourOneVersion() {
        createWidget("ep_widget_info");
    }

    @Override
    public void createFourTwoVersion(String table) {
        createWidget(table);
    }

    @Override
    public void createFourThreeVersion() {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            //新建角色表
            List<CreateTableVo.TableField> secondTableFields = new ArrayList<>();
            secondTableFields.add(new CreateTableVo.TableField( "id", FieldType.INT, 0,
                    true, false, true, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "paramcode", FieldType.VARCHAR, 30,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "paramname", FieldType.VARCHAR, 100,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "paramvalue", FieldType.CLOB, 0,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "memo", FieldType.VARCHAR, 300,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "paramgroupid", FieldType.VARCHAR, 20,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "groupdesc", FieldType.VARCHAR, 200,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "create_date", FieldType.DATE, 0,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "update_date", FieldType.DATE, 0,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "create_user", FieldType.VARCHAR, 30,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "update_user", FieldType.VARCHAR, 30,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "status", FieldType.VARCHAR, 12,
                    false, true, false, false, false));
            CreateTableVo secondTableVo = new CreateTableVo("ep_config_info", secondTableFields);
            StringBuilder secondSB = DaoUtils.createTable(secondTableVo, getDialect());
            pstmt = conn.prepareStatement(secondSB.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            secondSB.setLength(0);
            //索引
            pstmt = conn.prepareStatement("CREATE UNIQUE INDEX paramid ON ep_config_info(paramcode, paramgroupid)");
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            String[] codeList = new String[]{"list","list-header","page-border","page","func","page-sub-border"};
            String[] nameList = new String[]{"列表模板代码","列表header代码","页面代码","页面代码","功能代码","页面代码"};
            String[] valueList = new String[]{"<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                    "<list>\n\t<table>##tableName##</table>  \n\t<condition>##condition##</condition>\n" +
                    "\t##headers##\n\t<showTitle>true</showTitle>\n\t<pageSize>0</pageSize>\n" +
                    "\t<showPanel>true</showPanel>\n\t<showRowNumbers>true</showRowNumbers>\n" +
                    "\t<sizedByContent>false</sizedByContent>\n\t<span>false</span>\n\t<checkmark>false</checkmark>\n" +
                    "\t<multiple>false</multiple>\n</list>",
                    "<header>\n\t<name>##name##</name>\n\t<field>##name##</field>\n\t<title>##cnName##</title>\n" +
                    "\t<type>##type##</type>\n\t<isSort>false</isSort>\n\t<isVisible>true</isVisible>\n" +
                    "\t<totalType>0</totalType>\n</header>",
                    "<borderlayout>\n\t<west border=\"none\" size=\"30%\" margins=\"5,5,5,5\">\n" +
                    "\t\t<datalist entity=\"##list-west##\" id=\"pageList\" hflex=\"1\" vflex=\"1\" durable=\"true\" event=\"open('U')\" showRowNumbers=\"true\" showPanel=\"true\" pageSize=\"20\" sizedByContent=\"true\" span=\"true\" sclass=\"border-0\"/>\n" +
                    "\t</west>\n\t<center border=\"none\">\n\t\t<grid sclass=\"border-0 z-form\" oddRowSclass=\"none\" hflex=\"1\" vflex=\"1\">\n" +
                    "\t\t\t<columns>##columns##</columns>\n\t\t\t<rows>##rows##</rows>\n\t\t</grid>\n\t</center>\n</borderlayout>",
                    "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<page>\n\t<width>0</width>\n\t<height>0</height>\n" +
                    "\t<table>##table##</table>\n\t<onVisible>##onVisible##</onVisible>\n\t<ajax><![CDATA[##border##]]></ajax>\n" +
                    "</page>",
                    "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<task>\n\t<refId>##page##</refId>\n\t<processCode>C</processCode>\n" +
                    "\t<isUpdatable>true</isUpdatable>\n\t<isVisible>true</isVisible>\n</task>",
                    "<borderlayout>\n  <west border=\"none\" size=\"30%\" margins=\"5,5,5,5\">\n" +
                    "\t\t<datalist entity=\"##list-west##\" id=\"pageList\" hflex=\"1\" vflex=\"1\" durable=\"true\" event=\"open('U')\" showRowNumbers=\"true\" showPanel=\"true\" pageSize=\"20\" sizedByContent=\"true\" span=\"true\" sclass=\"border-0\"/>\n" +
                    "\t</west>\n  <center border=\"none\">\n    <borderlayout>\n      <north border=\"none\" margins=\"5,5,5,5\" size=\"30%\">\n" +
                    "        <grid sclass=\"border-0 z-form\" oddRowSclass=\"none\" hflex=\"1\" vflex=\"1\">\n" +
                    "\t\t\t<columns>##columns##</columns>\n\t\t\t<rows>##rows##</rows>\n\t\t</grid>\n" +
                    "      </north>\n      <center border=\"none\" hflex=\"1\" vflex=\"1\">\n        <vlayout  hflex=\"1\" vflex=\"1\">\n" +
                    "          <div>\n            <button label=\"新增明细\" event=\"detailList.create()\" iconSclass=\"z-icon-plus\"/>\n" +
                    "            <button label=\"删除明细\" event=\"detailList.remove()\" iconSclass=\"z-icon-minus\"/>\n" +
                    "          </div>\n" +
                    "          <datalist entity=\"##sub-list##\" id=\"detailList\" hflex=\"1\" vflex=\"1\" pageSize=\"20\" sclass=\"border-0\" editableColumns=\"##editableColumns##\" >\n" +
                    "            <attribute name=\"before\">##rel-cols##;\n            </attribute>\n" +
                    "          </datalist>\n        </vlayout>\n      </center>\n    </borderlayout>\n" +
                    "  </center>\n</borderlayout>"};
            //填入数据
            if (codeList.length > 0) {
                pstmt = conn
                        .prepareStatement("insert into ep_config_info (paramcode,paramname,paramvalue,paramgroupid,create_date) " +
                                "values(?,?,?,?,?)");
                for (int index = 0; index < codeList.length; index++) {
                    pstmt.setString(1, codeList[index]);
                    pstmt.setString(2, nameList[index]);
                    pstmt.setString(3, valueList[index]);
                    pstmt.setString(4, "auto-template");
                    pstmt.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
            }
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
    }

    protected boolean createWidget (String table) {
        PreparedStatement pstmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(ds);
            //新建角色表
            List<CreateTableVo.TableField> secondTableFields = new ArrayList<>();
            secondTableFields.add(new CreateTableVo.TableField( "widgetId", FieldType.INT, 0,
                    true, false, true, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "`name`", FieldType.VARCHAR, 50,
                    false, false, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "desp", FieldType.VARCHAR, 100,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "picture", FieldType.BLOB, 0,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "content", FieldType.CLOB, 0,
                    false, false, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "device", FieldType.CLOB, 0,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "createDate", FieldType.DATETIME, 0,
                    false, false, false, true, false));
            secondTableFields.add(new CreateTableVo.TableField( "updateDate", FieldType.DATE, 0,
                    false, true, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "createUser", FieldType.VARCHAR, 40,
                    false, false, false, false, false));
            secondTableFields.add(new CreateTableVo.TableField( "updateUser", FieldType.VARCHAR, 40,
                    false, true, false, false, false));
            CreateTableVo secondTableVo = new CreateTableVo( table, secondTableFields);
            StringBuilder secondSB = DaoUtils.createTable(secondTableVo, getDialect());
            pstmt = conn.prepareStatement(secondSB.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
            secondSB.setLength(0);
            //索引
            secondSB.append("CREATE INDEX ").append(table).append("_idx ON ").append(table).append("(widgetId)");
            pstmt = conn.prepareStatement(secondSB.toString());
            pstmt.execute();
            pstmt.close();
            pstmt = null;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(pstmt);
        }
        return true;
    }

    protected String backup(String table, DataSource dataSource) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            String tmpTable = table + "_"
                    + RandomStringUtils.randomAlphanumeric(10);
            StringBuilder sb = new StringBuilder();
			/*sb.append("CREATE TABLE ").append(tmpTable).append(" (SELECT ");
			for (TableField tf : fields)
				sb.append(tf.getName()).append(",");
			sb.deleteCharAt(sb.length() - 1);
			sb.append(" FROM ").append(table).append(")");*/
            sb.append("CREATE TABLE ").append(tmpTable).append(" LIKE ").append(table);
            System.out.println("backup:" + sb.toString());
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
            return tmpTable;
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }

    protected void restore(String source, String target, DataSource dataSource) {
        Statement stmt = null;
        try {
            Connection conn = JdbcTransactions.getConnection(dataSource);
            stmt = conn.createStatement();
            stmt.execute("drop table if exists " + target);
            stmt.close();
            stmt = null;
            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TABLE ").append(target)
                    .append(" LIKE ").append(source);
            stmt = conn.createStatement();
            stmt.execute(sb.toString());
        } catch (SQLException ex) {
            throw new DaoException(ex.getMessage());
        } finally {
            DaoUtils.closeQuietly(stmt);
        }
    }
}
